/*
 * @Author: xzy 1453077527@qq.com
 * @Date: 2025-08-24 00:34:58
 * @LastEditors: xzy 1453077527@qq.com
 * @LastEditTime: 2025-08-26 17:05:15
 * @Description:
 */
package com.xtedu.examproject.dao.impl;

import com.xtedu.examproject.dao.base.SubjectDao;
import com.xtedu.examproject.entity.Subject;
import com.xtedu.examproject.util.DBUtil;
import com.xtedu.examproject.util.PageHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class SubjectDaoImpl implements SubjectDao {

    @Override
    public int insert(Subject subject) throws Exception {
        String sql = "insert into subjects (sub_name, state) values (?, ?)";
        try (Connection conn = DBUtil.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, subject.getName());
            ps.setBoolean(2,subject.isState());
            return ps.executeUpdate();
        }
    }

    @Override
    public int update(Subject subject) throws Exception {
        String sql = "update subjects set sub_name=?,state=? where id=?";
        try (Connection conn = DBUtil.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1,subject.getName());
            ps.setBoolean(2,subject.isState());
            ps.setInt(3,subject.getId());
            return ps.executeUpdate();
        }
    }

    @Override
    public int delete(Subject subject) throws Exception {
        String sql = "delete from subjects where id = ?";
        try (Connection conn = DBUtil.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1,subject.getId());
            return ps.executeUpdate();
        }
    }

    @Override
    public int deleteById(int id) throws Exception {
        return 0;
    }

    @Override
    public Subject findById(int id) throws Exception {
        String sql = "select * from subjects where id = ?";
        try (Connection conn = DBUtil.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            Subject subject = new Subject();
            while (rs.next()) {
                subject.setId(rs.getInt("id"));
                subject.setName(rs.getString("sub_name"));
                subject.setState(rs.getBoolean("state"));
            }
            return subject;
        }
    }

    @Override
    public List<Subject> findAll() throws Exception {
       String sql = "select * from subjects";
       try(Connection con = DBUtil.getConnection();
           PreparedStatement ps = con.prepareStatement(sql)){
           ResultSet rs = ps.executeQuery();
           List<Subject> subjects = new ArrayList<>();
           while(rs.next()){
               Subject subject = new Subject();
               subject.setId(rs.getInt("id"));
               subject.setName(rs.getString("sub_name"));
               subject.setState(rs.getBoolean("state"));
               subjects.add(subject);
           }
           return subjects;
       }
    }

    @Override
    public PageHelper findAllByPage(int pageindex, int pagesize) throws Exception {
        String sql = "select * from subjects limit ?,?";
        List<Subject> subjects = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, (pageindex-1)*pagesize);
            ps.setInt(2, pagesize);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                Subject subject = new Subject();
                subject.setId(rs.getInt("id"));
                subject.setName(rs.getString("sub_name"));
                subject.setState(rs.getBoolean("state"));
                subjects.add(subject);
            }
        }catch (Exception e){
            throw new RuntimeException(e);
        }
        //分页返回
        String sql1 = "select ceil(count(*)/?) from subjects";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps1 = conn.prepareStatement(sql1)) {
            ps1.setInt(1,pagesize);
            ResultSet rs1 = ps1.executeQuery();
            int totalPages = 0;
            if(rs1.next()){
                totalPages = rs1.getInt(1);
            }
            return new PageHelper<>(subjects,pageindex,pagesize,totalPages,"",pageindex);
        }
    }

    @Override
    public PageHelper findPageByRoleIds(int pageindex, int pagesize, int... roleIds) throws Exception {
        return null;
    }

    @Override
    public PageHelper findByIdByPage(int subjectId,int pageIndex, int pageSize) throws Exception {
        String sql = "select * from subjects where id = ?";
        List<Subject> subjects = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, subjectId);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                Subject subject = new Subject();
                subject.setId(rs.getInt("id"));
                subject.setName(rs.getString("sub_name"));
                subject.setState(rs.getBoolean("state"));
                subjects.add(subject);
            }
        }
        //分页返回
        String sql1 = "select ceil(count(*)/?) from subjects where id = ?";
        try (Connection conn = DBUtil.getConnection();
        PreparedStatement ps1 = conn.prepareStatement(sql1)) {
            ps1.setInt(1,pageSize);
            ps1.setInt(2,subjectId);
            ResultSet rs1 = ps1.executeQuery();
            int totalPages = 0;
            if(rs1.next()){
                totalPages = rs1.getInt(1);
            }
            return new PageHelper<>(subjects,pageIndex,pageSize,totalPages,"",pageIndex);
        }
    }

    @Override
    public PageHelper findByNameByPage(String subjectName,int pageIndex, int pageSize) throws Exception {
        String sql = "select * from subjects where sub_name like ?";
        List<Subject> subjects = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1,"%"+subjectName+"%");
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                Subject subject = new Subject();
                subject.setId(rs.getInt("id"));
                subject.setName(rs.getString("sub_name"));
                subject.setState(rs.getBoolean("state"));
                subjects.add(subject);
            }
        }
        //分页返回
        String sql1 = "select ceil(count(*)/?) from subjects where sub_name like ?";
        try (Connection conn = DBUtil.getConnection();
        PreparedStatement ps1 = conn.prepareStatement(sql1)) {
            ps1.setInt(1,pageSize);
            ps1.setString(2,"%"+subjectName+"%");
            ResultSet rs1 = ps1.executeQuery();
            int totalPages = 0;
            while(rs1.next()){
                totalPages = rs1.getInt(1);
            }
            return new PageHelper<>(subjects, pageIndex, pageSize,totalPages,"", pageIndex);
        }
    }
}
